' These can then be opened like any normal table or bound
' to a data control or whatever.
' When done with them, you can delete them in preparation
' for running the stored proc again.
d.TableDefs.Delete ResultTable
d.TableDefs.Delete ResultTable & "1"
' Another thing that stored procedures sometimes do is
' use the Transact SQL "Print" command to return misc
' data. You can use those from Jet 2.0 also. The msgs
' will get written to a new table called "<username> - N".
' Thus:
Set s = d.CreateSnapshot("Run: Sproc w/ Print Statements")
' (Note: created with "Create: Sproc w/ Print Statements")
d.TableDefs.Refresh
Debug.Print d.TableDefs("Admin - 00").DateCreated
' Note that the table is only created on demand. If the
' print statement didn't occur until after all the results
' had been returned, then we would have to close the
' snapshot before they appeared.
s.Close
d.Close
End Sub
' Minimize Connections
'
' When working with ODBC data, it can help to be aware of your
' connection usage. Although Jet quietly manages these for
' you, you can tweak its handling with some of the tips below.
'
' + Preconnect at app startup to save time later
' + limit returned dynasets to 100 records
' + force connection release by completing dynaset filling
' + modify the ConnectionTimeout in [ODBC] section
' + Remember, connections can't be closed if:
' - a transaction is pending
' - not all query results have been fetched
'
Sub MinimizeConnections ()
' When you first reference an attached table, Jet will make a
' connection to the ODBC database. Since creating the
' connection can be an expensive operation which causes a
' pause at a bad time, you may wish to force it to be opened
' at some earlier time. The following code will force this:
Dim d As Database, ConnStr As String
ConnStr = "ODBC;" ' prompt the user for info
Set d = OpenDatabase("", False, False, ConnStr)
ConnStr = d.Connect ' save the filled in string for later
d.Close
' Note that your application should not use the database that
' you've opened above -- it should use attached tables. The
' connection created above will last until it is timed out
' (default is 10 mins).
End Sub
' Use parameterized queries instead of 'dynamic SQL'. Why?
' + they execute faster
' + they're easier to use than building SQL strings
'
Sub UseParameterizedQueries ()
' Say you wished to repeatedly find a set of records based
' on certain criteria. For example, say you wanted all
' Authors whose name started with a certain letter. One way
' to do this is to have code like:
Dim d As Database, ds As dynaset
Set d = OpenDatabase("biblio.mdb")
firstletter = "G"
sqlstr = "Select * from Authors where Author like """ & firstletter & "*"""
Set ds = d.CreateDynaset(sqlstr)
Debug.Print ds!Author
ds.Close
'A preferred way is to have a parameterized query already created
'and just set the parameter appropriately. For example:
'I'll create the query here so that you can use standard biblio.
'Normally it would already exist in the database
Dim qd As querydef
Set qd = d.CreateQueryDef("AuthorLike", "PARAMETERS FirstLetter Text; SELECT DISTINCTROW Authors.Au_ID, Authors.Author FROM Authors WHERE ((Authors.Author Like [FirstLetter]))")
qd.Close
' This is where the code starts that you would normally write
Set qd = d.OpenQueryDef("AuthorLike")
qd!firstletter = firstletter & "*"
Set ds = qd.CreateDynaset()
Debug.Print ds!Author
ds.Close
' As you can see, the code is shorter, easier to understand,
' easier to maintain -- and, actually runs faster as well!
qd.Close ' tidy up after demo
d.DeleteQueryDef ("AuthorLike")
End Sub
' Its best to use SQL statements whereever possible rather
' than explicit coding. Why?
' + the query engine has tighter binding to the actual isam
' than VB code
' + the query engine has access to certain optimized operations
' such as bulk copy operations
' + SQL statements are quicker and easier to debug than the
